ALTER  proc sp_inventory_intransit_all_report
@process_step_id int,
@process_step_type varchar(15),
@date_From varchar(10),
@date_To   varchar(10)


as


declare maincursor cursor for
 select ProcessStep_ID from product where P_Marketing_Part_Number is not null
 declare @main_id int
 open maincursor
 fetch next from maincursor into @main_id
 while @@fetch_status = 0

begin

declare @results Table(PO int, IN_NO varchar(50), NextLoc varchar(50), 
SH_date datetime, Qty int, MAWB varchar(50), HWAB varchar(50), 
FtNo varchar(50), ETD datetime, ETA datetime, Typename varchar (50), 
partno varchar(50), p_id int, lotid int, Lot_no varchar (50))

declare processcursor cursor for select ProcessStep_ID from fn_get_process_flow_by_id (@main_id)
declare @p_id int
open processcursor
fetch next from processcursor into @p_id
while @@fetch_status = 0
	begin
	declare @po int, @lotid int, @pp_id int, @qty int
	declare @IN_NO varchar (50), @NextLoc varchar(50),@MAWB varchar (50), @HAWB varchar (50)
	declare @FtNo varchar (50), @typename varchar (50), @partno varchar (50), @lotno varchar (50)
	declare @Shdate datetime, @etd datetime, @eta datetime
		declare lotcursor cursor for 
		select TLT_Customer_PO, TLT_Invoice_NUmber, TLT_NextLocation, 
		TLT_Ship_Date, TLT_Ship_Qty, TLT_MAWB_Number, TLT_HAWB_Number, 
		TLT_Flight_Number, TLT_ETD, TLT_ETA, 
		PS_Type_Name, Part_Number, process_step.processStep_id,
		lot_id,Lot_Number from 
		process_step, product, transit_lot_table,lot_table where 
		lot_table.Lot_ID = transit_lot_table.TLT_Lot_ID and 
		lot_table.Lot_Current_Status = 'transit' and 
		lot_table.ProcessStep_ID = product.ProcessStep_ID and 
		lot_table.ProcessStep_ID = process_step.ProcessStep_ID and 
		lot_table.ProcessStep_ID = @p_id
		open lotcursor
		fetch next from lotcursor into
		@po, @IN_NO,@NextLoc,@Shdate,@qty,@MAWB,@HAWB,@FtNo,@etd,@eta,
		@typename,@partno,@pp_id,@lotid,@lotno
		while @@fetch_status =0
			begin
     			insert into @results values(@po, @IN_NO,@NextLoc,@Shdate, @qty, @MAWB, @HAWB, @FtNo,
			@etd, @eta,@typename, @partno, @pp_id, @lotid, @lotno)
			fetch next from lotcursor into
			@po, @IN_NO,@NextLoc,@Shdate,@qty,@MAWB,@HAWB,@FtNo,@etd,@eta,
			@typename,@partno,@pp_id,@lotid,@lotno
			end
		close lotcursor
		deallocate lotcursor
		
	fetch next from processcursor into @p_id
end
close processcursor
deallocate processcursor

fetch next from maincursor into @main_id
end
close maincursor
deallocate maincursor
--select * from @results
if @process_step_type ='0' and @date_From = '0' and @date_To= '0'
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results
end
else if @process_step_type <>'0' and @date_From = '0' and @date_To= '0'
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where Typename = @process_step_type
end
else if @process_step_type ='0' and @date_From <> '0' and @date_To= '0' 
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where SH_date >= @date_From
end
else if @process_step_type ='0' and @date_From = '0' and @date_To<> '0' 
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where SH_date <= @date_To
end
else if @process_step_type <>'0' and @date_From <> '0' and @date_To= '0' 
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where SH_date >= @date_From and Typename = @process_step_type
end
else if @process_step_type <>'0' and @date_From = '0' and @date_To<> '0' 
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where SH_date <= @date_To and Typename = @process_step_type
end
else if @process_step_type ='0' and @date_From <> '0' and @date_To<> '0'  
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where SH_date >= @date_From and SH_date <= @date_To
end
else if  @process_step_type <>'0' and @date_From <> '0' and @date_To<> '0'  
begin
select distinct Lot_no, PO, IN_NO, NextLoc,SH_date, Qty, MAWB, HWAB, 
FtNo, ETD, ETA, Typename, partno, p_id, lotid from @results where SH_date >= @date_From and SH_date <= @date_To and Typename = @process_step_type
end


